package com.F03.action;


import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.F03.actionform.F0303ActionForm;
import com.F03.actionform.F0304ActionForm;
import com.F03.dao.PdPlanningDAO;
import com.F03.info.PdPlanningInfo;
import com.action.BaseAction;
import com.config.Constants;
import com.info.T_S_YHCZXXinfo;
import com.util.CommonTools;
import com.util.ExcelExport;

/**
 * 
 * @file F0904Action.java
 * @author huqy
 * @since 2009-3-15
 * @version 1.0
 */
public class F0304Action extends BaseAction {

	/**
	 * Method execute
	 * @param mapping
	 * @param form
	 * @param request
	 * @param response
	 * @return ActionForward
	 */
	Logger log = Logger.getLogger(F0304Action.class);

	/** 操作记录INFO */
	T_S_YHCZXXinfo t_YhczxxInfo = null;
	
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response){
		F0304ActionForm actionForm = (F0304ActionForm) form;
		PdPlanningDAO dao = new PdPlanningDAO();
		PdPlanningInfo info = new PdPlanningInfo();
		String operationType = actionForm.getOperationType();
		String strList = "list";
		
		try {
			//操作记录
			t_YhczxxInfo = setYhczxx(request, response, operationType,"F0304");

			/*
			if(Constants.BACK.equals(operationType)){
				return mapping.findForward(strList);
			}
			*/

			if(Constants.QUERYLIST.equals(operationType)){
				info.setManufacturingOrder1(CommonTools.null2String(actionForm.getManufacturingOrder1()));
				info.setManufacturingOrder2(CommonTools.null2String(actionForm.getManufacturingOrder2()));
				info.setLastAdvanceDate1(CommonTools.null2String(actionForm.getLastAdvanceDate1()));
				info.setLastAdvanceDate2(CommonTools.null2String(actionForm.getLastAdvanceDate2()));
				doQueryList(actionForm,dao.queryProductionReportList(info),dao.queryTotalList(info));
				//actionForm.setList(dao.queryProductionReportList(info));
				//actionForm.setTotalList(dao.queryTotalList(info));
				return mapping.findForward(strList);
			}
			else if(Constants.INIT.equals(operationType)){
				return mapping.findForward(strList);
			}
			else if("export".equals(operationType)){
				doExport(actionForm, dao, response);
				return mapping.findForward("list");
			}
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			if (dao != null) dao.disConnect();
		}
		return mapping.findForward(strList);
	}
	
	private void doQueryList(F0304ActionForm actionForm,List list,List totalList){
		if(list!=null&&list.size()>0){
			actionForm.setList(list);
			actionForm.setTotalList(totalList);
		}
		else{
			actionForm.setList(list);
			actionForm.setTotalList(totalList);
			actionForm.setErrorFlag("2");
		}	
	}
	
	private void doExport(F0304ActionForm actionForm, PdPlanningDAO dao, HttpServletResponse response){
		List titleList = new ArrayList();
		try {
			String fileName = "生产情况报表";
			/* 标题设定 */
			titleList.add("工作号");
			titleList.add("产品模型");
			titleList.add("组");
			titleList.add("数量");
			titleList.add("当前流程");
			titleList.add("更新时间");
			titleList.add("销售单价(rmb)");
			titleList.add("总售价(rmb)");
			titleList.add("成本单价(rmb)");
			titleList.add("总成本(rmb)");
			/* sql作成 */
			String sql = getSql(actionForm);
			String sqlTotal = getSqlTotal(actionForm);
			/* excel输出 */
			
			ExcelExport.exportTotal(fileName, titleList, sql,sqlTotal, response);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (dao != null)
				dao.disConnect();
		}
	}
	
	public String getSql(F0304ActionForm actionForm){
		StringBuffer sql=new StringBuffer();
		sql.append(" select a.manufacturing_order,i.name,a.groups, a.group_qty,adyh.FUN_GETPHASES(a.last_advance) as last_advance_name, ");
    	sql.append(" convert(varchar(10),a.last_advance_date,120) as last_advance_date,l.unit_price,l.total_price, ");
    	sql.append(" adyh.FUN_GETMATERIALCAST(b.serial,c.code) as unit_cost_price, ");
    	sql.append(" (adyh.FUN_GETMATERIALCAST(b.serial,c.code)*group_qty) as total_cost_price ");
    	sql.append("  from pd_production a,t_family b,t_not_standard_products c,");
    	sql.append(" t_product_model i, c_orders l");
    	sql.append(" where a.manufacturing_order = c.manufacturing_order ");
    	if(!actionForm.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >='"+actionForm.getManufacturingOrder1()+"' ");
    	}
    	if(!actionForm.getManufacturingOrder2().equals("")){
    		 sql.append(" and a.manufacturing_order <='"+actionForm.getManufacturingOrder2()+"' ");
    	}
        sql.append(" and a.last_advance = b.phases_code and ");
        sql.append(" b.family_code = c.family_code and ");
        sql.append(" c.model_type = i.code and ");
        sql.append(" l.manufacturing_order = a.manufacturing_order and ");
        sql.append(" b.serial = adyh.FUN_GETMAXSERIAL(a.manufacturing_order,'"+actionForm.getLastAdvanceDate1()+"','"+actionForm.getLastAdvanceDate2()+"')  ");
 
		return sql.toString();
	}
	
	public String getSqlTotal(F0304ActionForm actionForm){
		StringBuffer sql=new StringBuffer();
		sql.append(" select '总计','','',sum(a.group_qty) AS total_group_qty,'','', ");
    	sql.append(" sum(convert(float,l.unit_price)) as total_unit_price, ");
    	sql.append(" sum(CONVERT(float,l.total_price)) as total_total_price, ");
    	sql.append(" sum(convert(float,adyh.FUN_GETMATERIALCAST(b.serial,c.code))) as total_unit_cost_price, ");
    	sql.append("  sum(CONVERT(float,(adyh.FUN_GETMATERIALCAST(b.serial,c.code)*group_qty))) as total_total_cost_price ");
    	sql.append(" from pd_production a,t_family b,t_not_standard_products c, ");
        sql.append("  t_product_model i, c_orders l  ");
        sql.append(" where a.manufacturing_order = c.manufacturing_order ");
        if(!actionForm.getManufacturingOrder1().equals("")){
        	sql.append(" and a.manufacturing_order >='"+actionForm.getManufacturingOrder1()+"' ");
        }
        if(!actionForm.getManufacturingOrder2().equals("")){
        	
        	sql.append("  and a.manufacturing_order <='"+actionForm.getManufacturingOrder2()+"' ");
        }
        sql.append(" and a.last_advance = b.phases_code and ");
        sql.append(" b.family_code = c.family_code and ");
        sql.append(" c.model_type = i.code and ");
        sql.append(" l.manufacturing_order = a.manufacturing_order and ");
        sql.append("  b.serial = adyh.FUN_GETMAXSERIAL(a.manufacturing_order,'"+actionForm.getLastAdvanceDate1()+"','"+actionForm.getLastAdvanceDate2()+"')  ");

		return sql.toString();
	}
}
